<?php
    /* @var $this SiteController */

    $this->pageTitle = Yii::app()->name;
?>

<?php /*
      $this->beginWidget('bootstrap.widgets.TbHeroUnit', array(
      'heading' => 'Welcome to ' . CHtml::encode(Yii::app()->name),
      ));
      ?>

      <p>Congratulations! You have successfully created your Yii application.</p>

      <?php $this->endWidget(); ?>

      <p>You may change the content of this page by modifying the following two files:</p>

      <ul>
      <li>View file: <code><?php echo __FILE__; ?></code></li>
      <li>Layout file: <code><?php echo $this->getLayoutFile('main'); ?></code></li>
      </ul>

      <p>For more details on how to further develop this application, please read
      the <a href="http://www.yiiframework.com/doc/">documentation</a>.
      Feel free to ask in the <a href="http://www.yiiframework.com/forum/">forum</a>,
      should you have any questions.</p>
     *
     */ ?>

<div class='row-fluid'>
    <div class='span6'>
        <?php
            $this->widget('bootstrap.widgets.TbLabel', array(
                'type' => 'info', // 'success', 'warning', 'important', 'info' or 'inverse'
                'label' => t('New customers of today'),
            ));
            $user = User::model()->findByPk(Yii::app()->user->id);
            if ($user->superuser) {
                //user is superuser
                $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM mylong_customers where date(createdate) = curdate()')->queryScalar();
                $sql = 'SELECT * FROM mylong_customers where date(createdate) = curdate()';
            } else {
                //user is not superuser
                $count = Yii::app()->db->createCommand("SELECT COUNT(*) FROM mylong_customers where date(createdate) = curdate() and left(customer_code,3)='$user->user_code'")->queryScalar();
                $sql = "SELECT * FROM mylong_customers where date(createdate) = curdate() and left(customer_code,3)='$user->user_code'";
            };
            $dataProvider = new CSqlDataProvider($sql, array(
                //'keyField' => 'customer_code',
                'totalItemCount' => $count,
                'sort' => array(
                    'attributes' => array(
                        'customer_code',
                    ),
                ),
                'pagination' => array(
                    'pageSize' => 10,
                ),
            ));
            $this->widget('bootstrap.widgets.TbGridView', array(
                'type' => 'striped bordered condensed',
                'dataProvider' => $dataProvider,
                'template' => '{items}',
                'columns' => array(
                    array('name' => 'customer_code', 'header' => t('Customer Code')),
                    array('name' => 'firstname', 'header' => t('First Name')),
                    array('name' => 'lastname', 'header' => t('Last Name')),
                    array('name' => 'createdate', 'header' => t('Create Date'), 'type' => 'datetime'),
                    array('name' => 'last_loan_num', 'header' => t('Loans')),
                    array(
                        'class' => 'bootstrap.widgets.TbButtonColumn',
                        'template' => '{update}',
                        'buttons' => array(
                            'update' => array(
                                'url' => 'Yii::app()->createURL(\'customers/update\', array(\'id\' => $data["id"]))',
                            ),
                        ),
                    ),
                ),
            ));
        ?>
    </div>
    <div class='span6'>
        <?php
            $this->widget('bootstrap.widgets.TbLabel', array(
                'type' => 'info', // 'success', 'warning', 'important', 'info' or 'inverse'
                'label' => t('New customers of the month') . ': ' . date('n') . '/' . date('Y'),
            ));
            $user = User::model()->findByPk(Yii::app()->user->id);
            if ($user->superuser) {
                //user is superuser
                $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM mylong_customers where year(createdate) = year(curdate()) and month(createdate) = month(curdate())')->queryScalar();
                $sql = 'SELECT * FROM mylong_customers where year(createdate) = year(curdate()) and month(createdate) = month(curdate())';
            } else {
                //user is not superuser
                $count = Yii::app()->db->createCommand("SELECT COUNT(*) FROM mylong_customers where year(createdate) = year(curdate()) and month(createdate) = month(curdate()) and left(customer_code,3)='$user->user_code'")->queryScalar();
                $sql = "SELECT * FROM mylong_customers where year(createdate) = year(curdate()) and month(createdate) = month(curdate()) and left(customer_code,3)='$user->user_code'";
            };
            $dataProvider = new CSqlDataProvider($sql, array(
                //'keyField' => 'customer_code',
                'totalItemCount' => $count,
                'sort' => array(
                    'attributes' => array(
                        'customer_code',
                    ),
                ),
                'pagination' => array(
                    'pageSize' => 10,
                ),
            ));
            $this->widget('bootstrap.widgets.TbGridView', array(
                'type' => 'striped bordered condensed',
                'dataProvider' => $dataProvider,
                'template' => '{items}',
                'columns' => array(
                    array('name' => 'customer_code', 'header' => t('Customer Code')),
                    array('name' => 'firstname', 'header' => t('First Name')),
                    array('name' => 'lastname', 'header' => t('Last Name')),
                    array('name' => 'createdate', 'header' => t('Create Date'), 'type' => 'datetime'),
                    array('name' => 'last_loan_num', 'header' => t('Loans')),
                    array(
                        'class' => 'bootstrap.widgets.TbButtonColumn',
                        'template' => '{update}',
                        'buttons' => array(
                            'update' => array(
                                'url' => 'Yii::app()->createURL(\'customers/update\', array(\'id\' => $data["id"]))',
                            ),
                        ),
                    ),
                ),
            ));
        ?>
    </div>
</div>
<div class='row-fluid'>
    <div class='span6'>
        <?php
            $this->widget('bootstrap.widgets.TbLabel', array(
                'type' => 'info', // 'success', 'warning', 'important', 'info' or 'inverse'
                'label' => t('Summary of today'),
            ));
            $user = User::model()->findByPk(Yii::app()->user->id);
            if ($user->superuser) {
                //user is superuser
                $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM sum_main_table where on_date = curdate()')->queryScalar();
                $sql = "Select b.user_code, ifnull(c.cash_received, 0) as cash_received, ifnull(c.cash_out, 0) as cash_out, ifnull(c.cash_expenses, 0) as cash_expenses, ifnull((c.cash_received - c.cash_out - c.cash_expenses), 0) as total From (Select user_code, curdate() As on_date From mylong_users Where user_code <> '') As b Left Join sum_main_table As c On b.user_code = c.user_code And b.on_date = c.on_date order by b.user_code";
            } else {
                //user is not superuser
                $count = Yii::app()->db->createCommand("SELECT COUNT(*) FROM sum_main_table where on_date = curdate() and user_code='$user->user_code'")->queryScalar();
                $sql = "Select b.user_code, ifnull(c.cash_received, 0) as cash_received, ifnull(c.cash_out, 0) as cash_out, ifnull(c.cash_expenses, 0) as cash_expenses, ifnull((c.cash_received - c.cash_out - c.cash_expenses), 0) as total From (Select user_code, curdate() As on_date From mylong_users Where user_code = '$user->user_code') As b Left Join sum_main_table As c On b.user_code = c.user_code And b.on_date = c.on_date";
            };
            $dataProvider = new CSqlDataProvider($sql, array(
                'keyField' => 'user_code',
                'totalItemCount' => $count,
                'sort' => array(
                    'attributes' => array(
                        'user_code',
                    ),
                ),
                'pagination' => array(
                    'pageSize' => 10,
                ),
            ));
            $this->widget('bootstrap.widgets.TbGridView', array(
                'type' => 'striped bordered condensed',
                'dataProvider' => $dataProvider,
                'template' => '{items}',
                'columns' => array(
                    array('name' => 'user_code', 'header' => t('User Code')),
                    array('name' => 'cash_received', 'header' => t('Cash Received')),
                    array('name' => 'cash_out', 'header' => t('Cash Out')),
                    array('name' => 'cash_expenses', 'header' => t('Expenses')),
                    array('name' => 'total', 'header' => t('Total')),
                ),
            ));
        ?>
    </div>
    <div class='span6'>
        <?php
            $this->widget('bootstrap.widgets.TbLabel', array(
                'type' => 'info', // 'success', 'warning', 'important', 'info' or 'inverse'
                'label' => t('Summary of the month') . ': ' . date('n') . '/' . date('Y'),
            ));
            $user = User::model()->findByPk(Yii::app()->user->id);
            if ($user->superuser) {
                //user is superuser
                $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM sum_main_table where year(on_date) = year(curdate()) and month(on_date) = month(curdate())')->queryScalar();
                $sql = 'SELECT user_code, cash_received, cash_out, cash_expenses, (cash_received - cash_out - cash_expenses) as total FROM sum_main_table where year(on_date) = year(curdate()) and month(on_date) = month(curdate())';
            } else {
                //user is not superuser
                $count = Yii::app()->db->createCommand("SELECT COUNT(*) FROM sum_main_table where year(on_date) = year(curdate()) and month(on_date) = month(curdate()) and user_code='$user->user_code'")->queryScalar();
                $sql = "SELECT user_code, cash_received, cash_out, cash_expenses, (cash_received - cash_out - cash_expenses) as total FROM sum_main_table where year(on_date) = year(curdate()) and month(on_date) = month(curdate()) and user_code='$user->user_code'";
            };
            $dataProvider = new CSqlDataProvider($sql, array(
                'keyField' => 'user_code',
                'totalItemCount' => $count,
                'sort' => array(
                    'attributes' => array(
                        'user_code',
                    ),
                ),
                'pagination' => array(
                    'pageSize' => 10,
                ),
            ));
            $this->widget('bootstrap.widgets.TbGridView', array(
                'type' => 'striped bordered condensed',
                'dataProvider' => $dataProvider,
                'template' => '{items}',
                'columns' => array(
                    array('name' => 'user_code', 'header' => t('User Code')),
                    array('name' => 'cash_received', 'header' => t('Cash Received')),
                    array('name' => 'cash_out', 'header' => t('Cash Out')),
                    array('name' => 'cash_expenses', 'header' => t('Expenses')),
                    array('name' => 'total', 'header' => t('Total')),
                ),
            ));
        ?>
    </div>
</div>
